package com.css.fxfzypg.modules.baseData.repository.impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.entity.DisasterScene;
import com.css.fxfzypg.modules.baseData.queryParams.DisasterSceneQueryParams;
import com.css.fxfzypg.modules.baseData.repository.DisasterSceneRepository;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class DisasterSceneRepositoryImpl implements DisasterSceneRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public DisasterScene updateDisasterScene(DisasterScene disasterScene) {

        String sql = "update sys_disasterscene set ds_province = :dsProvince, ds_city = :dsCity, ds_counties = :dsCounties, ds_destroy = :dsDestroy, ds_casualty = :dsCasualty ,ds_geology = :dsGeology,ds_secondary = :dsSecondary,ds_traffic = :dsTraffic,ds_else = :dsElse,ds_updatetime = :dsUpdatetime ,province_id = :provinceId,city_id =  :cityId , counties_id = :countiesId  where ds_id = '"+disasterScene.getDsId()+"'" ;

        SqlParameterSource source = new BeanPropertySqlParameterSource(disasterScene);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql,source,keyHolder);
        return disasterScene;
    }

    @Override
    @Transactional
    public DisasterScene saveDisasterScene(DisasterScene disasterScene) {
        String sql = "insert into sys_disasterscene values(:dsId,:dsProvince,:dsCity,:dsCounties,:dsDestroy,:dsCasualty,:dsGeology,:dsSecondary,:dsTraffic,:dsElse,:dsStatus,:dsCreatetime,:dsUpdatetime,:provinceId,:cityId,:countiesId)";

        SqlParameterSource source = new BeanPropertySqlParameterSource(disasterScene);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return disasterScene;
    }

    @Override
    public Map<String,Object> getAllDisasterSceneParams(DisasterSceneQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select  d.* from sys_disasterscene d ");

        StringBuilder orderSql = new StringBuilder(" order by d.ds_province");

        StringBuilder whereSql = new StringBuilder(" where d.ds_status = '" + YNEnum.Y.toString() + "'");

        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and u.province_id ='"+queryParams.getProvinceId()+"'");
        }

        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and u.city_id  ='" + queryParams.getCityId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCountiesId())) {
            whereSql.append(" and u.counties_id ='" + queryParams.getCountiesId() + "'");
        }

        int firstIndex=(queryParams.getCurPage()-1) * queryParams.getPageSize();
        int lastIndex=queryParams.getCurPage() * queryParams.getPageSize();
        sql.append(whereSql).append(orderSql).toString();
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DisasterScene.class)).size();
        List<DisasterScene> disasterScenes = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(DisasterScene.class));
        Map<String,Object> map=new HashMap();
        map.put("total",total);
        map.put("rows",disasterScenes);
        return map;
    }

    @Override
    public DisasterScene getDisasterSceneById(String id) {
        String sql = "select * from sys_disasterscene where ds_id = '"+id+"' and ds_status = '"+YNEnum.Y+"'";
        DisasterScene disasterScene = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DisasterScene.class));
        return disasterScene;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deleteDisasterScenesById(List<String> ids, Timestamp timestamp) {
        String sql = "update sys_disasterscene set ds_status = '" + YNEnum.N + "' , ds_updatetime = '"+timestamp+"' " + " where ds_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

//    @Override
//    public List<DisasterScene> getAll() {
//        return jdbcTemplate.query("select * from sys_disasterscene where ds_status  = '"+YNEnum.Y+"'",new BeanPropertyRowMapper<>(DisasterScene.class));
//    }

//    @Override
//    public void importExcel(List<DisasterScene> elevations) {
//        jdbcTemplate.batchUpdate("insert into sys_elevation (elevation_id,elevation_number,elevation_band,elevation_nodatavalue,elevation_min,elevation_max,elevation_status)values(?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
//            @Override
//            public int getBatchSize() {
//                return elevations.size();
//            }
//            @Override
//            public void setValues(PreparedStatement ps, int i) throws SQLException {
//                ps.setString(1, elevations.get(i).getElevationId());
//                ps.setInt(2,elevations.get(i).getElevationNumber());
//                ps.setString(3,elevations.get(i).getElevationBand());
//                ps.setString(4,elevations.get(i).getElevationNodatavalue());
//                ps.setInt(5,elevations.get(i).getElevationMin());
//                ps.setInt(6,elevations.get(i).getElevationMax());
//                ps.setString(7,elevations.get(i).getElevationStatus());
//            }
//        });
//    }

}
